﻿/// @author: Bruno Figueira 11234
/// @author: Luis Pereira 12784
/// @last updated: 26-01-2015

using System;
using System.Collections.Generic;
using System.Data.Linq;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace srccsharp.model
{
    /// <summary>
    /// Cria a conexao com a base de dados SQLite e realiza todas
    /// as operacoes relacionadas com a mesma
    /// </summary>
    class DBManager
    {
        private string DATABASE_PATH = @"Data Source=content\database.db";
        private DataContext context;

        /// <summary>
        /// Construtor da classe DBManager
        /// </summary>
        public DBManager()
        {
            var connection = new SQLiteConnection(DATABASE_PATH);
            this.context = new DataContext(connection);
        }

        /// <summary>
        /// Adiciona uma linha de valores à tabela
        /// </summary>
        /// <param name="ipc">Objeto IPC que identifica uma linha
        /// de valores da tabela</param>
        public void InsertIPC(IPC ipc)
        {
            var dados_ipc = this.context.GetTable<IPC>();
            dados_ipc.InsertOnSubmit(ipc);
            this.context.SubmitChanges();
        }

        /// <summary>
        /// Apaga os dados existentes na tabela
        /// </summary>
        public void ClearTable()
        {
            var dados_ipc = this.context.GetTable<IPC>();
            IEnumerable<IPC> dadosIPCtoDelete = (from c in dados_ipc select c).ToList();
            dados_ipc.DeleteAllOnSubmit(dadosIPCtoDelete);
        }

        /// <summary>
        /// Retorna os dados existentes na tabela
        /// </summary>
        /// <returns></returns>
        public List<IPC> GetAllData() 
        {
            return this.context.GetTable<IPC>().ToList();
        }

        public List<IPC> GetDataFilteredByYears(int startYear, int endYear)
        {
            var filterYears = this.context.GetTable<IPC>().Where(ipc => ipc.ano >= startYear && ipc.ano <= endYear).ToList();

            return filterYears;
        }
    }
}
